﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;
using System.Web.Security;
using AgileNYCWebService.Manager.Data.Table;
using System.Data.SqlClient;

namespace AgileNYCWebService.Manager.Data
{
    internal class DataManager
    {
        private readonly string _userToken;

        public DataManager()
            : this(string.Empty)
        {
        }

        public DataManager(string userToken)
        {
            _userToken = userToken;
        }

        public JsonResult Login(Users user)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(user.Email))
                {
                    if (Regex.IsMatch(user.Email,
                        @"\A(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)\Z"))
                    {
                        var mobileWorkingSystem = (Enums.MobileWorkingSystem)user.MobileWorkingSystem;
                        var authorizationType = (Enums.AuthorizationType)user.SocialType;
                        var deviceOs = (Enums.DeviceOs)user.DeviceOs;

                        if (authorizationType != Enums.AuthorizationType.Email)
                            user.Password = Membership.GeneratePassword(12, 0);

                        if (!string.IsNullOrEmpty(user.Password))
                        {
                            string userToken = Guid.NewGuid().ToString().Replace("-", string.Empty);
                            var procedure = new StoredProcedure("Users_Login");
                            procedure.ParameterAdd("UserToken", userToken);
                            procedure.ParameterAdd("Email", user.Email.GetString(string.Empty));
                            procedure.ParameterAdd("Password", user.Password.GetString(string.Empty));
                            procedure.ParameterAdd("SocialType", authorizationType);
                            procedure.ParameterAdd("IsCheck", user.IsCheck.GetBoolean(false));
                            procedure.ParameterAdd("DeviceOs", deviceOs);
                            procedure.ParameterAdd("DeviceId", user.DeviceId.GetString(string.Empty));
                            procedure.ParameterAdd("MobileWorkingSystem", mobileWorkingSystem);

                            DataTable table = procedure.GetDataTable();
                            if (table != null && table.Rows.Count > 0)
                            {
                                Users userDetails = null;
                                foreach (DataRow row in table.Rows)
                                {
                                    userDetails = new Users(row);
                                    break;
                                }

                                if (String.IsNullOrEmpty(userDetails.Status))
                                {
                                    jsonResult.Error = string.Empty;
                                    jsonResult.Result = JsonHelper.JsonSerializer(userDetails);
                                }
                                else
                                {
                                    jsonResult.Error = userDetails.Status;
                                    jsonResult.Result = null;
                                }
                            }
                            else
                            {
                                jsonResult.Error = "Incorrect email or passwor";
                                jsonResult.Result = null;
                            }
                        }
                        else
                        {
                            jsonResult.Error = "Password is empty";
                            jsonResult.Result = null;
                        }
                    }
                    else
                    {
                        jsonResult.Error = "Not a valid e-mail";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Email is empty";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("LOGIN ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        private byte[] ReadToEnd(System.IO.Stream stream)
        {
            long originalPosition = 0;

            if (stream.CanSeek)
            {
                originalPosition = stream.Position;
                stream.Position = 0;
            }

            try
            {
                byte[] readBuffer = new byte[4096];

                int totalBytesRead = 0;
                int bytesRead;

                while ((bytesRead = stream.Read(readBuffer, totalBytesRead, readBuffer.Length - totalBytesRead)) > 0)
                {
                    totalBytesRead += bytesRead;

                    if (totalBytesRead == readBuffer.Length)
                    {
                        int nextByte = stream.ReadByte();
                        if (nextByte != -1)
                        {
                            byte[] temp = new byte[readBuffer.Length * 2];
                            Buffer.BlockCopy(readBuffer, 0, temp, 0, readBuffer.Length);
                            Buffer.SetByte(temp, totalBytesRead, (byte)nextByte);
                            readBuffer = temp;
                            totalBytesRead++;
                        }
                    }
                }

                byte[] buffer = readBuffer;
                if (readBuffer.Length != totalBytesRead)
                {
                    buffer = new byte[totalBytesRead];
                    Buffer.BlockCopy(readBuffer, 0, buffer, 0, totalBytesRead);
                }
                return buffer;
            }
            finally
            {
                if (stream.CanSeek)
                {
                    stream.Position = originalPosition;
                }
            }
        }

        public JsonResult Photo(Stream userPhoto)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var procedure = new StoredProcedure("Users_UpdatePhoto");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("ImageFile", Utility.StreamToByte(userPhoto));
                    if (procedure.GetScalar().GetBoolean(false))
                    {
                        jsonResult.Error = string.Empty;
                        jsonResult.Result = Utility.GetPhotoURL(GetUserDetailsObject().Code);
                    }

                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Photo";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error(" PHOTO ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        private UserDetails GetUserDetailsObject()
        {
            var procedure = new StoredProcedure("Users_Select");
            procedure.ParameterAdd("UserToken", _userToken);
            DataTable table = procedure.GetDataTable();
            if (table != null && table.Rows.Count > 0)
            {
                Users userDetails = null;
                foreach (DataRow row in table.Rows)
                {
                    userDetails = new Users(row);
                    break;
                }
                return userDetails;
            }
            return null;
        }

        public JsonResult GetUserDetails()
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var userDetails = this.GetUserDetailsObject();
                    if (userDetails != null)
                    {
                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer(userDetails);
                    }
                    else
                    {
                        jsonResult.Error = "Unknown User";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("GET USER DETAILS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult SaveUserDetails(Users userDetails)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (userDetails != null && !string.IsNullOrEmpty(_userToken))
                {
                    var procedure = new StoredProcedure("Users_Update");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Email", userDetails.Email);
                    procedure.ParameterAdd("FirstName", userDetails.FirstName.GetString(string.Empty));
                    procedure.ParameterAdd("Password", userDetails.Password.GetString(string.Empty));
                    procedure.ParameterAdd("LastName", userDetails.LastName.GetString(string.Empty));
                    procedure.ParameterAdd("Phone", userDetails.Phone.GetString(string.Empty));
                    procedure.ParameterAdd("Title", userDetails.Title.GetString(string.Empty));
                    procedure.ParameterAdd("Company", userDetails.Company.GetString(string.Empty));
                    procedure.ParameterAdd("Site", userDetails.Site.GetString(string.Empty));
                    procedure.ParameterAdd("City", userDetails.City.GetString(string.Empty));
                    procedure.ParameterAdd("State", userDetails.State.GetString(string.Empty));
                    procedure.ParameterAdd("Info", userDetails.Info.GetString(string.Empty));
                    procedure.ParameterAdd("AdditionalInfo", userDetails.AdditionalInfo.GetString(string.Empty));                   
                    procedure.ParameterAdd("FacebookId", userDetails.FacebookId.GetString(string.Empty));
                    procedure.ParameterAdd("GooglePlusId", userDetails.GooglePlusId.GetString(string.Empty));
                    procedure.ParameterAdd("LinkedInId", userDetails.LinkedInId.GetString(string.Empty));
                    procedure.ParameterAdd("FacebookName", userDetails.FacebookName.GetString(string.Empty));
                    procedure.ParameterAdd("GooglePlusName", userDetails.GooglePlusName.GetString(string.Empty));
                    procedure.ParameterAdd("LinkedInName", userDetails.LinkedInName.GetString(string.Empty));

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = procedure.GetScalar().GetBoolean(false).ToString();
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("SAVE USER DETAILS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult GetEvents(int offset, int limit, bool isPast)
        {
            var jsonResult = new JsonResult();
            try
            {

                var scheduleList = new List<Events>();
                var procedure = new StoredProcedure("Events_Select");
                procedure.ParameterAdd("UserToken", _userToken);
                procedure.ParameterAdd("Offset", offset);
                procedure.ParameterAdd("Limit", limit);
                procedure.ParameterAdd("IsPast", isPast);

                DataTable table = procedure.GetDataTable();
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        scheduleList.Add(new Events(row));
                    }
                }

                jsonResult.Error = string.Empty;
                jsonResult.Result = JsonHelper.JsonSerializer<List<Events>>(scheduleList);

            }
            catch (Exception ex)
            {
                Logger.Log.Error("GET EVENTS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public JsonResult CheckIn(int eventId, bool isCheckIn)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!String.IsNullOrEmpty(_userToken) && eventId > 0)
                {
                    var procedure = new StoredProcedure("Events_CheckIn");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("EventId", eventId);
                    procedure.ParameterAdd("IsCheckIn", isCheckIn);
                    DataTable table = procedure.GetDataTable();
                    Events eventData = null;
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            eventData = new Events(row);
                            break;
                        }
                    }

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = eventData == null ? "" : JsonHelper.JsonSerializer<Events>(eventData);

                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Invalid Event Id";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("CHECK IN", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public JsonResult Rating(int eventId, float ratingValue)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!String.IsNullOrEmpty(_userToken) && eventId > 0)
                {
                    var procedure = new StoredProcedure("Ratings_Insert");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("EventId", eventId);
                    procedure.ParameterAdd("RatingValue", ratingValue);
                    DataTable table = procedure.GetDataTable();
                    Events eventData = null;
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            eventData = new Events(row);
                            break;
                        }
                    }
                    if (eventData == null)
                    {
                        jsonResult.Error = "The event is not Checked In by User";
                        jsonResult.Result = "";
                    }
                    else
                    {
                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<Events>(eventData);
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Invalid Event Id";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("CHECK IN", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public JsonResult GetEvent(int eventId)
        {
            var jsonResult = new JsonResult();
            try
            {
                var procedure = new StoredProcedure("Events_GetById");
                procedure.ParameterAdd("UserToken", _userToken);
                procedure.ParameterAdd("EventId", eventId);
                Events eventDetails = null;
                DataTable table = procedure.GetDataTable();
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        eventDetails = new Events(row);
                        break;
                    }
                }

                if (eventDetails != null)
                {
                    jsonResult.Error = string.Empty;
                    jsonResult.Result = JsonHelper.JsonSerializer<Events>(eventDetails);
                }
                else
                {
                    jsonResult.Error = "Invalid Event";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("GET EVENTS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }
        public JsonResult SendNotification(int code)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken) && code > 999 && code < 10000)
                {
                    var procedure = new StoredProcedure("Notifications_Send");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Code", code);
                    DataTable dt = procedure.GetDataTable();
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            var userDetails = new Users(row);
                            Enums.UserStatus status = Enums.UserStatus.UnknownUser;

                            string result = userDetails.Status.ToLowerInvariant();
                            if (result.Equals(Enums.UserStatus.Waiting.GetDescription().ToLowerInvariant()))
                            {
                                status = Enums.UserStatus.Waiting;
                                var notificationManager = new NotificationManager();
                                if (userDetails.DeviceOs > -1 && userDetails.DeviceOs < 3 && !string.IsNullOrEmpty(userDetails.DeviceId))
                                {
                                    switch (userDetails.DeviceOs)
                                    {
                                        case 0:
                                            notificationManager.SendNotificationToApple(userDetails, false, false);
                                            break;
                                        case 1:
                                            notificationManager.SendNotificationToAndroid(userDetails, false, false, code);
                                            break;
                                    }
                                }
                            }
                            if (result.Equals(Enums.UserStatus.Confirm.GetDescription().ToLowerInvariant()))
                                status = Enums.UserStatus.Confirm;
                            if (result.Equals(Enums.UserStatus.Cancel.GetDescription().ToLowerInvariant()))
                                status = Enums.UserStatus.Cancel;
                            if (result.Equals(Enums.UserStatus.AlreadyAdded.GetDescription().ToLowerInvariant()))
                                status = Enums.UserStatus.AlreadyAdded;
                            if (result.Equals(Enums.UserStatus.UnknownUser.GetDescription().ToLowerInvariant()))
                                status = Enums.UserStatus.UnknownUser;
                            if (result.Equals(Enums.UserStatus.CannotAddYourself.GetDescription().ToLowerInvariant()))
                                status = Enums.UserStatus.CannotAddYourself;

                            jsonResult.Error = string.Empty;
                            jsonResult.Result = status.GetDescription();

                            break;
                        }
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown User";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("SEND NOTIFICATION ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public void ReplyNotification(int code, bool agree)
        {
            if (!string.IsNullOrEmpty(_userToken))
            {
                var procedure = new StoredProcedure("Notifications_Reply");
                procedure.ParameterAdd("UserToken", _userToken);
                procedure.ParameterAdd("Code", code);
                procedure.ParameterAdd("Agree", agree);
                DataTable dt = procedure.GetDataTable();
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        var userDetails = new Users(row);

                        string result = userDetails.Status.ToLowerInvariant();
                        if (!result.Equals(Enums.UserStatus.UnknownUser.GetDescription().ToLowerInvariant()) && !result.Equals(Enums.UserStatus.UnknownUser.GetDescription().ToLowerInvariant()))
                        {
                            var notificationManager = new NotificationManager();
                            if (userDetails.DeviceOs > -1 && userDetails.DeviceOs < 3 && !string.IsNullOrEmpty(userDetails.DeviceId))
                            {
                                switch (userDetails.DeviceOs)
                                {
                                    case 0:
                                        notificationManager.SendNotificationToApple(userDetails, true, agree);
                                        break;
                                    case 1:
                                        notificationManager.SendNotificationToAndroid(userDetails, true, agree, code);
                                        break;
                                }
                            }
                        }

                        break;
                    }
                }
            }
        }

        public JsonResult CheckNotification()
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var notificationResults = new List<Users>();
                    var procedure = new StoredProcedure("Notifications_Check");
                    procedure.ParameterAdd("UserToken", _userToken);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            var user = new Users(row);
                            if (user.Code > 0)
                                notificationResults.Add(new Users(row));
                        }
                    }

                    if (notificationResults.Count > 0)
                    {
                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<List<Users>>(notificationResults);
                    }
                    else
                    {
                        jsonResult.Error = string.Empty;
                        jsonResult.Result = "No Notification";
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("CONTACTS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public JsonResult Contacts(int offset, int limit)
        {
            var jsonResult = new JsonResult();

            try
            {
                if (!String.IsNullOrEmpty(_userToken))
                {
                    var userDetails = new List<Users>();
                    var procedure = new StoredProcedure("Contacts_Select");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Offset", offset);
                    procedure.ParameterAdd("Limit", limit);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            userDetails.Add(new Users(row));

                        }
                    }
                    jsonResult.Error = string.Empty;
                    jsonResult.Result = JsonHelper.JsonSerializer<List<Users>>(userDetails);
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("CONTACTS ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }
            return jsonResult;
        }

        public JsonResult ContactsSync(DateTime modifiedDate)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var contactList = new List<Contacts>();
                    var procedure = new StoredProcedure("Contacts_Sync");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("ModifiedDate", modifiedDate);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            contactList.Add(new Contacts(row));
                        }

                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<List<Contacts>>(contactList);
                    }
                    else
                    {
                        jsonResult.Error = "Empty Result";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Contacts SYNC ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult EventsSync(DateTime modifiedDate)
        {
            var jsonResult = new JsonResult();
            try
            {
                var eventList = new List<Events>();
                var procedure = new StoredProcedure("Events_Sync");
                procedure.ParameterAdd("UserToken", _userToken);
                procedure.ParameterAdd("ModifiedDate", modifiedDate);
                DataTable table = procedure.GetDataTable();
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        eventList.Add(new Events(row));
                    }

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = JsonHelper.JsonSerializer<List<Events>>(eventList);
                }
                else
                {
                    jsonResult.Error = "Empty Result";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Contacts SYNC ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult ParticipantsSync(DateTime modifiedDate)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var participantsList = new List<Participants>();
                    var procedure = new StoredProcedure("Participants_Sync");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("ModifiedDate", modifiedDate);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            participantsList.Add(new Participants(row));
                        }

                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<List<Participants>>(participantsList);
                    }
                    else
                    {
                        jsonResult.Error = "Empty Result";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Contacts SYNC ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult RatingsSync(DateTime modifiedDate)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var ratingsList = new List<Ratings>();
                    var procedure = new StoredProcedure("Ratings_Sync");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("ModifiedDate", modifiedDate);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            ratingsList.Add(new Ratings(row));
                        }

                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<List<Ratings>>(ratingsList);
                    }
                    else
                    {
                        jsonResult.Error = "Empty Result";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Contacts SYNC ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult UsersSync(DateTime modifiedDate)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var usersList = new List<Users>();
                    var procedure = new StoredProcedure("Users_Sync");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("ModifiedDate", modifiedDate);
                    DataTable table = procedure.GetDataTable();
                    if (table != null && table.Rows.Count > 0)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            usersList.Add(new Users(row));
                        }

                        jsonResult.Error = string.Empty;
                        jsonResult.Result = JsonHelper.JsonSerializer<List<Users>>(usersList);
                    }
                    else
                    {
                        jsonResult.Error = "Empty Result";
                        jsonResult.Result = null;
                    }
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Contacts SYNC ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult AddNote(Note noteData)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken) && !string.IsNullOrEmpty(noteData.NoteText) && noteData.Code > 999 && noteData.Code < 10000)
                {
                    var procedure = new StoredProcedure("Contacts_Update");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Code", noteData.Code);
                    procedure.ParameterAdd("Note", noteData.NoteText);

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = procedure.GetScalar().ToString();
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Code or noteText is empty";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Add Note ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult DeleteNote(Note noteData)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken) && noteData.Code > 999 && noteData.Code < 10000)
                {
                    var procedure = new StoredProcedure("Contacts_Update");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Code", noteData.Code);
                    procedure.ParameterAdd("Note", null);

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = procedure.GetScalar().ToString();
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Code";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Add Note ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult DeletePhoto()
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken))
                {
                    var procedure = new StoredProcedure("Users_DeletePhoto");
                    procedure.ParameterAdd("UserToken", _userToken);

                    procedure.Exec();

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = true.ToString();
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Add Note ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public JsonResult DeleteContact(int code)
        {
            var jsonResult = new JsonResult();
            try
            {
                if (!string.IsNullOrEmpty(_userToken) && code > 999 && code < 10000)
                {
                    var procedure = new StoredProcedure("Contacts_Delete");
                    procedure.ParameterAdd("UserToken", _userToken);
                    procedure.ParameterAdd("Code", code);

                    jsonResult.Error = string.Empty;
                    jsonResult.Result = procedure.GetScalar().ToString();
                }
                else
                {
                    jsonResult.Error = "Unknown UserToken or Code";
                    jsonResult.Result = null;
                }
            }
            catch (Exception ex)
            {
                Logger.Log.Error("Add Note ", ex);
                jsonResult.Error = "Error is initialization";
                jsonResult.Result = null;
                return jsonResult;
            }

            return jsonResult;
        }

        public object GetPhotoBytes(int code)
        {
            var procedure = new StoredProcedure("Users_GetImageFile");
            procedure.ParameterAdd("Code", code);

            return procedure.GetScalar();
        }

        public JsonResult CheckEmail(string email) {
            var jsonResult = new JsonResult();
            try
            {
                var procedure = new StoredProcedure("Users_CheckByEmail");
                procedure.ParameterAdd("Email", email);
                jsonResult.Error = string.Empty;
                jsonResult.Result = procedure.GetScalar().ToString();
                return jsonResult;
            }
            catch (Exception ex) {
                Logger.Log.Error("Add Note ", ex);
                jsonResult.Error = ex.Message;
                jsonResult.Result = null;
                return jsonResult;
            }
        }

    }

}